﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;

using Jzy.Model.Project;
using Frame.Lib.Core.Base;
using Frame.Lib.Utilities;
using Frame.Lib.Core;

namespace Jzy.DAL.Project
{
    public class ProjectDAL : BaseDAL
    {
        public void SetYear(out int minYear, out int maxYear,params PrjStatus[] status)
        {
            string sql = "select year(min(StartTime)) as minYear,year(max(StartTime)) as maxYear from " + OTableName.Project;
            if (status.Length > 0)
            {
                string tmp = "";
                foreach (PrjStatus sta in status)
                {
                    tmp += "," + (int)sta;
                }
                tmp = tmp.Trim(',');
                sql += " where Status in (" + tmp + ")";
            }

            DataTable dt = dbOper.Query(sql);
            if (dt.Rows[0][0] is DBNull)
            {
                minYear = maxYear = DateTime.Now.Year;
            }
            else
            {
                minYear = (int)dt.Rows[0]["minYear"];
                maxYear = (int)dt.Rows[0]["maxYear"];
            }
        }

        public DataTable GetDataTable(int status)
        {
            string sql = "select * from " + OTableName.Project + " where Status = @p0";
            DataTable dt = dbOper.Query(sql, status);
            return dt;
        }

        /// <summary>
        /// 获得项目列表，除了已归档和作废的
        /// </summary>
        /// <param name="year"></param>
        /// <param name="status">为-1是可得全部状态</param>
        /// <param name="userID"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string year, int status, int? userID)
        {
            string sql = "select * from " + OTableName.Project + " where 1=1 ";

            if (year != null && year != "")
            {
                sql += " and year(StartTime) = @p0";
            }

            if (status != -1)
            {
                sql += " and Status = " + status;
            }
            else
            {
                sql += " and Status <> " + (int)PrjStatus.归档 + " and Status <> " + (int)PrjStatus.作废;
            }

            if (userID != null)
            {
                sql += " and (CreatorID = " + userID + " or PMID = " + userID + " or ID in (select PrjID from " + OTableName.PrjMember + " where UserID = " + userID + "))";
            }

            DataTable dt = string.IsNullOrEmpty(year) ? dbOper.Query(sql) : dbOper.Query(sql, year);

            return dt;
        }

        public List<int> GetMemberIDs(object id)
        {
            string sql = "select UserID from " + OTableName.PrjMember + " where PrjID = @p0";
            List<int> ids = dbOper.Query<int>(sql, id);
            return ids;
        }

        public void SaveMemberIDs(object id, List<int> memberIDs)
        {
            string[] sql = new string[memberIDs.Count + 1];
            sql[0] = "delete from " + OTableName.PrjMember + " where PrjID = " + id;
            for (int i = 0; i < memberIDs.Count; i++)
            {
                sql[i + 1] = "insert into " + OTableName.PrjMember + "(PrjID,UserID) values(" + id + "," + memberIDs[i] + ")";
            }

            dbOper.RunSql(sql);
        }

        public DataTable GetPrjMembers(string prjID)
        {
            string sql = "select UserID,(select Name from " + STableName.User + " where ID = PrjMember.UserID) as UserName from " + OTableName.PrjMember + " where PrjID = " + prjID;
            sql += " union select PMID,(select Name from " + STableName.User + " where ID = Project.PMID) as UserName from " + OTableName.Project + " where ID = " + prjID;
            DataTable dt = dbOper.Query(sql);
            return dt;
        }

        public List<int> GetAllTaskID(string prjID)
        {
            string sql = "select ID from " + OTableName.PrjTask + " where PrjID = " + prjID;
            List<int> ids = dbOper.Query<int>(sql);
            return ids;
        }

        public void SetStatus(int id, PrjStatus status)
        {
            string sql = "update " + OTableName.Project + " set Status=@p0 where ID = @p1";
            dbOper.RunSql(sql, (int)status, id);
        }

        public void SetStatus(List<int> ids, PrjStatus status)
        {
            string strIDs = ConvertUtil.ConvertToDbStr(ids);
            string sql = "update " + OTableName.Project + " Set Status=@p0 where ID in (" + strIDs + ")";
            dbOper.RunSql(sql, (int)status);
        }

        
    }
}
